import datetime as dt

from apscheduler.schedulers.background import BackgroundScheduler
from apscheduler.triggers.cron import CronTrigger

from app.moudules8.erp_purch import AutoGet_data
from app.moudules9.Supply_chain_data import get_Supply_chain_data, extract_Supply_chain_data, save_data_to_database
from logger_config import cron_logger as logger


def check_data_exists(date_str):
    """
    检查指定日期的数据是否已存在
    
    Args:
        date_str (str): 日期字符串，格式为 'YYYY-MM-DD'
        
    Returns:
        bool: 如果数据存在返回True，否则返回False
    """
    from DB.DB import DB
    connection = None
    try:
        connection = DB()
        with connection.conn.cursor() as cursor:
            # 查询指定日期是否存在数据
            sql = "SELECT COUNT(*) as count FROM c_supply_chain_daily_data WHERE record_date = %s"
            cursor.execute(sql, (date_str,))
            result = cursor.fetchone()

            # 修复KeyError: 处理不同数据库返回格式
            if result is None:
                return False

            # 处理字典格式返回结果 (如MySQL with DictCursor)
            if isinstance(result, dict):
                count = result.get('count', 0)
            # 处理元组格式返回结果 (如MySQL with默认cursor)
            elif isinstance(result, (tuple, list)) and len(result) > 0:
                count = result[0]
            else:
                logger.warning(f"未知的查询结果格式: {type(result)}, 值: {result}")
                return False

            return count > 0
    except Exception as e:
        import traceback
        logger.error(f"检查数据是否存在时发生错误: {str(e)}")
        logger.error(traceback.format_exc())
        return False
    finally:
        if connection:
            # 连接会在DB对象销毁时自动关闭
            pass


# 全局调度器实例
_scheduler = None


def get_scheduler(app):
    """获取或创建调度器实例"""
    global _scheduler
    if _scheduler is None:
        _scheduler = configure_scheduler(app)
    return _scheduler

def configure_scheduler(app):
    """配置定时任务调度器"""
    # 创建调度器
    scheduler = BackgroundScheduler(timezone='Asia/Shanghai')

    # 添加供应链数据同步任务（每天下午13点51分执行）
    @scheduler.scheduled_job(CronTrigger(hour=15, minute=15), id='supply_chain_sync_task', name='供应链数据同步任务')
    def sync_previous_day_supply_chain_data():
        """
        同步前一天的供应链数据
        """
        try:
            # 计算前一天的日期
            previous_day = dt.date.today() - dt.timedelta(days=1)
            previous_day_str = previous_day.strftime("%Y-%m-%d")

            # 检查前一天的数据是否已存在
            if check_data_exists(previous_day_str):
                logger.info(f"{previous_day_str} 的供应链数据已存在，跳过同步")
                return True

            start_date_str = previous_day.strftime("%Y-%m-%d 00:00:00")
            end_date_str = previous_day.strftime("%Y-%m-%d 23:59:59")
            date_range = f"{start_date_str},{end_date_str}"

            logger.info(f"开始同步 {previous_day} 的供应链数据")

            # 获取数据 - 添加重试机制处理I/O错误
            max_retries = 3
            retry_count = 0
            response = None

            while retry_count < max_retries:
                try:
                    response = get_Supply_chain_data(date_range)
                    break
                except Exception as io_error:
                    retry_count += 1
                    logger.warning(f"获取供应链数据失败 (第{retry_count}次重试): {io_error}")
                    if retry_count >= max_retries:
                        raise io_error
                    # 等待一段时间后重试
                    import time
                    time.sleep(5)

            if response is None:
                raise Exception("获取供应链数据失败，所有重试均失败")

            extracted_data = extract_Supply_chain_data(response.json())

            # 保存数据 - 添加重试机制
            retry_count = 0
            while retry_count < max_retries:
                try:
                    save_data_to_database(extracted_data, previous_day.strftime("%Y-%m-%d"))
                    break
                except Exception as db_error:
                    retry_count += 1
                    logger.warning(f"保存数据失败 (第{retry_count}次重试): {db_error}")
                    if retry_count >= max_retries:
                        raise db_error
                    # 等待一段时间后重试
                    import time
                    time.sleep(3)

            logger.info(f"成功同步 {previous_day} 的供应链数据，共处理 {len(extracted_data)} 条记录")
            return True
        except Exception as e:
            import traceback
            logger.error(f"同步前一天供应链数据时发生错误: {e}")
            logger.error(traceback.format_exc())
            return False

    # 添加采购数据同步任务（每天早上9点执行）
    @scheduler.scheduled_job(CronTrigger(hour=9, minute=0), id='purch_data_sync_task', name='采购数据同步任务')
    def sync_purch_data():
        """
        同步采购数据
        """
        try:
            logger.info("开始执行采购数据同步任务")
            AutoGet_data()
            logger.info("采购数据同步任务执行完成")
            return True
        except Exception as e:
            logger.error(f"执行采购数据同步任务时发生错误: {e}")
            import traceback
            logger.error(traceback.format_exc())
            return False

    return scheduler